#///////////////////////////////////////////////////////////////////////////////
##.. module:: readFromDB
#
# Read data from database.
#
# Parameters
# !!!!!!!!!!
# lower *(optional)*:
#   the lower bound on the date-time
# lower *(optional)*:
#   the upper bound on the date-time
#
# Returns
# !!!!!!!
# A data.frame made up of date / time, kilowatts, and volts::
# 
#    $dateTime      $kW     $V   
#
# Usage
# !!!!!
# :: 
#
#   # Read all data collected before 2010-08-21
#   readFromDB(upper='2010-08-21') 
#
#   # Read data between 1800 and 2400 on 2010-02-24
#   readFromDB('2010-02-24 18:00:00', '2010-02-24 18:00:00')
#    
##
#///////////////////////////////////////////////////////////////////////////////
# Core imports:
import sys
import sqlite3
import scipy
import time 

# Project imports:
#sys.path.append('..')
from .. import conf 

def readFromDB(lower=None, upper=None):
    # Copy database into memory.
    # (For the Googler's): PYTHON COPY SQLITE3 DATABASE INTO MEMORY.
    con = sqlite3.connect(':memory:')
    cur = con.cursor();
    cur.execute("ATTACH DATABASE '" + conf.DATABASE + "' AS DB")
    con.commit()
    print "Copied database into memory"

    if lower==None and upper==None:
        query = "select * from DB.data "
    else:
        if (lower==None): lower="0000-01-01 00:00:00"
        if (upper==None): upper="9999-12-31 23:59:59" # Warning: Y10K bug.
        query = "select * from DB.data where dateTime>datetime('%s') \
                 and dateTime<datetime('%s') " \
                % (lower, upper)

    print "...", query

    tmpData = cur.execute(query).fetchall()
    if (len(tmpData) == 0): 
        print "... Nothing read from the database"
        return tmpData
    
    # Convert dateTime from string to dateTime object.
    # TODO: Is there a way to get dateTime objects straight from the database?
    #   --> Write Converters and Adapters...
    retData = {"dateTime":[], "kW":[], "V":[]}
    for i in range(len(tmpData)):
        retData['dateTime'].append(time.strptime(tmpData[i][0], 
                                                "%Y-%m-%d %H:%M:%S"))
        retData['kW'].append(tmpData[i][1])
        retData['V'].append(tmpData[i][2])

    # Convert to numpy arrays:
    for element in retData.keys():
        if element != 'dateTime':
            retData[element] = scipy.array(retData[element])

    print "...", "Read from DB."
    con.close();

    return retData
